SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [SOI].tvf_SOI ( -- Add the parameters for the function here
																@FundId VARCHAR(20)
															, @ExportId INT 
	
															)
RETURNS TABLE
AS
RETURN
	( SELECT
			id.InstrClass
		, iss.IssuerName
		, soi.GetFootNotes(MAX(a.assetIntrnlId), @ExportId) FootNotes
		, MAX(iss.SubSector) Sector
		, soi.GetFormattedRate(MAX(a.AssetIntrnlId), @ExportId) 'Rate'
		, NULLIF(CAST(CAST(MAX(i.FLOOR) * 100 AS DECIMAL(38, 1)) AS VARCHAR(10))
							+ '%', '0.0%') AS FLOOR
		
		, CAST(MONTH(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/'
			+ CAST(DAY(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/'
			+ RIGHT(CAST(YEAR(MAX(i.MaturityDate)) AS VARCHAR(4)),
							2) Maturity
		
		,
            --MAX(CONVERT(VARCHAR(8), i.MaturityDate,1)) Maturity,
			CASE	WHEN SUM(a.EndAdjAmtFor) > 0
						THEN SUM(a.EndAdjAmtFor)
						ELSE CASE	WHEN MAX(a.FundCreditInstrumentId) IS NOT NULL
											THEN SUM(a.TotalCredit)
											ELSE SUM(a.EndAdjAmt)
									END
			END AS 'Principal Amount'
		
		, CASE	WHEN SUM(a.EndAdjAmtFor) > 0
						THEN SUM(a.EndAdjAmtFor * a.AmortCostPctPar)
						ELSE CASE	WHEN MAX(a.FundCreditInstrumentId) IS NOT NULL
											THEN SUM(a.TotalCredit)
											ELSE SUM(a.AmortCost)
									END
			END AS 'Amortized Cost'
		
		, CASE	WHEN MAX(a.FundCreditInstrumentId) IS NOT NULL
						THEN SUM([a].TotalCredit) + SUM([a].Mktval)
						ELSE SUM(a.MktVal)
			END AS 'Fair Value'
		
		, MAX(i.BaseRateType) 'BaseRateType'
		, [ah].UnfundedbyPfSort
		  --, a.FundCreditInstrumentId
		, i.InstrumentId
		, [a].[AssetType]
		FROM
			FSFundModel_Valcomm.Reporting.Asset a
			INNER JOIN FSFundModel_Valcomm.Reporting.Instrument i ON i.ExportId = a.ExportId
																						AND a.InstrumentId = i.InstrumentId
			INNER JOIN [ReportingScripts].[Valcomm].[AssetHelper](@ExportId) ah ON [ah].[AssetIntrnlId] = [a].[AssetIntrnlId]
			--LEFT JOIN FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci 
			--ON fci.ExportId = a.ExportId 
			--AND fci.FundCreditInstrumentId = a.FundCreditInstrumentId 
			INNER JOIN FSFundModel_Valcomm.Reporting.Issuer iss ON iss.ExportId = a.ExportId
																						AND i.IssuerId = iss.IssuerId
			INNER JOIN FSFundModel_Valcomm.Reporting.InstrumentDefinition id ON i.InstDefId = id.InstDefId
																						AND id.ExportId = a.ExportId
		WHERE
			a.ExportId = @ExportId
			AND ( a.IsActiveEndOfDay = 1
						OR a.FundCreditInstrumentId IS NOT NULL
					)
		GROUP BY  
	--		a.FundCreditInstrumentId,
			i.InstrumentId
		, iss.IssuerName
		, id.InstrClass
		, ah.UnfundedbyPfSort
		, [a].[AssetType])
GO
